package com.qixiang.ox.user.utils.poi;


import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @name: PoiUtil
 * @description:
 * @author: Yang Xingfu
 * @date: 2020-08-13 13:56
 **/
public class PoiUtil {

    public static Workbook getWorkBook(MultipartFile multipartFile) {
        Workbook workbook = null;
        try {
            if (multipartFile.getOriginalFilename().endsWith("xls")) {
                workbook = new HSSFWorkbook(multipartFile.getInputStream());
            } else if (multipartFile.getOriginalFilename().endsWith("xlsx")) {
                workbook = new XSSFWorkbook(multipartFile.getInputStream());
            }
        } catch (Exception e) {
            throw new RuntimeException("文件格式错误");
        }
        return workbook;
    }

    public static String getStringValue(Cell cell) {

        String stringCellValue = null;
        try {
            stringCellValue = getStringCellValue(cell);
        } catch (Exception e) {
            stringCellValue = "";
        }

        return stringCellValue;
    }

    public static int getIntValue(Cell cell) {
        int intCellValue = -1;
        try {
            String stringCellValue = getStringCellValue(cell);
            intCellValue = Integer.parseInt(stringCellValue);
        } catch (Exception e) {
            throw new RuntimeException("数据格式错误");
        }

        return intCellValue;
    }

    public static long getLongValue(Cell cell) {
        long longCellValue = -1L;
        try {
            String stringCellValue = getStringCellValue(cell);
            longCellValue = Long.parseLong(stringCellValue);
        } catch (Exception e) {
            throw new RuntimeException("数据格式错误");
        }

        return longCellValue;
    }

    public static float getFloatValue(Cell cell) {
        float floatCellValue = -1F;
        try {
            String stringCellValue = getStringCellValue(cell);
            floatCellValue = Float.parseFloat(stringCellValue);
        } catch (Exception e) {
            throw new RuntimeException("数据格式错误");
        }

        return floatCellValue;
    }

    public static double getDoubleValue(Cell cell) {
        double doubleCellValue = -1D;
        try {
            String stringCellValue = getStringCellValue(cell);
            doubleCellValue = Double.parseDouble(stringCellValue);
        } catch (Exception e) {
            throw new RuntimeException("数据格式错误");
        }

        return doubleCellValue;
    }


    public static BigDecimal getBigDecimalValue(Cell cell) {
        BigDecimal bigDecimalCellValue = null;
        try {
            String stringCellValue = getStringCellValue(cell);
            bigDecimalCellValue = new BigDecimal(stringCellValue);
        } catch (Exception e) {
            throw new RuntimeException("数据格式错误");
        }

        return bigDecimalCellValue;
    }

    public static boolean getBooleanValue(Cell cell) {
        boolean booleanCellValue = false;
        String ONE = "1";
        String ZERO = "0";
        try {
            String stringCellValue = getStringCellValue(cell);
            if (ONE.equals(stringCellValue)) {
                booleanCellValue = true;
            } else if (ZERO.equals(stringCellValue)) {
                booleanCellValue = false;
            } else {
                booleanCellValue = Boolean.parseBoolean(stringCellValue);
            }
        } catch (Exception e) {
            throw new RuntimeException("数据格式错误");
        }

        return booleanCellValue;
    }

    public static Date getDateValue(Cell cell) {
        Date date = null;
        String empty = "";
        try {
            String stringCellValue = getStringCellValue(cell);
            if (stringCellValue.isEmpty() || empty.equals(stringCellValue)) {
                return date;
            }
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            date = sdf.parse(stringCellValue);
        } catch (Exception e) {
            throw new RuntimeException("数据格式错误");
        }

        return date;
    }


    public static String getStringCellValue(Cell cell) {

        //  cell类型
        if (cell == null) {
            return "";
        }
        CellType cellType = cell.getCellType();

        String cellValue = "";

        // 判断数据的类型
        switch (cellType) {
            //  数字、日期类型
            case NUMERIC:
                //  日期类型
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    cellValue = sdf.format(cell.getDateCellValue());
                    //  数字类型
                } else {
                    cellValue = String.valueOf(new BigDecimal(cell.getNumericCellValue()));
                }
                break;
            //  字符串类型
            case STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            //  Boolean类型
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            //  公式类型
            case FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            //  空值类型
            case BLANK:
                cellValue = cell.getStringCellValue();
                break;
            //  错误类型
            case ERROR:
                cellValue = "非法字符";
                break;
            default:
                //  默认类型
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    public static boolean isRowEmpty(Row row) {
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i); {
                if (cell != null && cell.getCellType() != CellType.BLANK) {
                    return false;
                }
            }
        }
        return true;
    }

    public static Sheet getAccurateRowSheet(Workbook workbook) {
        return getAccurateRowSheet(workbook, 0, 0);
    }

    public static Sheet getAccurateRowSheet(Workbook workbook, int startRow) {
        return getAccurateRowSheet(workbook, 0, startRow);
    }

    public static Sheet getAccurateRowSheet(Workbook workbook, int sheetAt, int startRow) {
        Sheet sheet = workbook.getSheetAt(sheetAt);
        for (int i = startRow; i < sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (isRowEmpty(row)) {
                int lastRowNum = sheet.getLastRowNum();
                if (i >= startRow && i < lastRowNum) {
                    // 将行号为i+1一直到行号为lastRowNum的单元格全部上移一行，以便删除i行
                    sheet.shiftRows(i + 1, lastRowNum, -1);
                }
                if (i == lastRowNum) {
                    if (row != null) {
                        sheet.removeRow(row);
                    }
                }
                i--;
            }
        }
        return sheet;
    }

}
